/**
 * 
 */
package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.rowset.serial.SerialBlob;

import sk.stuba.fiit.foo07.genex.beans.Picture;
import sk.stuba.fiit.foo07.genex.exceptions.PictureInPictureQuestionException;

/**
 * @author Radu
 * 
 */

public class PictureDaoDerby extends BasicDao implements PictureDao {

    protected SerialBlob sblob;
    protected Picture pic;
    protected ResultSet rs;
    protected PreparedStatement pstmt;
    protected ArrayList<Picture> p_list;
    private static final String sqlDelPicFromQuestion = "DELETE FROM PICTUREQUESTION WHERE pictureID=? AND questionID=?";
    private static final String sqlAddPicToQuestion = "INSERT INTO PICTUREQUESTION VALUES (?,?)";
    private static final String sqlGetByQuestion = "SELECT pictureID FROM PictureQuestion WHERE QuestionId=?";
    private static final String sqlGetAll = "SELECT pictureId FROM Picture";
    private static final String sqlGetByTest = "SELECT PictureQuestion.pictureId FROM PictureQuestion, TestQuestion WHERE TestQuestion.questionID=PictureQuestion.questionID AND TestQuestion.testId=?";
    private static final String sqlGet = "SELECT * FROM Picture WHERE pictureID=?";
    private static final String sqlDeleteAll = "DELETE FROM Picture WHERE PictureID=?";
    private static final String sqlDelete = "DELETE FROM Picture WHERE PictureID=?";
    private static final String sqlAddAll = "INSERT INTO PICTURE VALUES (?,?,?,?,?,DEFAULT )";
    private static final String sqlAdd = "INSERT INTO PICTURE VALUES (?,?,?,?,?,DEFAULT )";
    private static final String sqlUpdate = "UPDATE PICTURE SET name=?, description = ?, settings = ?, mimetype = ?, content = ? WHERE pictureID = ?";
    private static final String sqlExistsName = "SELECT * FROM Picture WHERE name=?";

    /**
     * @param con
     */
    public PictureDaoDerby(Connection con) {
        super(con);

    }

    @Override
    public void addPicture(Picture toAdd) throws SQLException {
        try {

            sblob = new SerialBlob(toAdd.getContent());
            pstmt = con.prepareStatement(sqlAdd,
                    Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, toAdd.getName());
            pstmt.setString(2, toAdd.getDescription());
            pstmt.setString(3, toAdd.getSettings());
            pstmt.setString(4, toAdd.getMimeType());
            pstmt.setBlob(5, sblob.getBinaryStream());
            pstmt.execute();
            rs = pstmt.getGeneratedKeys();
            rs.next();
            toAdd.setPictureID(rs.getInt(1));
            rs.close();

        } catch (SQLException e) {
            pstmt = null;
            sblob = null;
            rs = null;
            throw e;
        }
    }

    @Override
    public void addPictures(ArrayList<Picture> toAdd) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlAddAll,
                    Statement.RETURN_GENERATED_KEYS);
            int len = toAdd.size();
            for (int i = 0; i < len; i++) {
                sblob = new SerialBlob(toAdd.get(i).getContent());
                pstmt.setString(1, toAdd.get(i).getName());
                pstmt.setString(2, toAdd.get(i).getDescription());
                pstmt.setString(3, toAdd.get(i).getSettings());
                pstmt.setString(4, toAdd.get(i).getMimeType());
                pstmt.setBlob(5, sblob.getBinaryStream());
                pstmt.execute();
                rs = pstmt.getGeneratedKeys();
                rs.next();
                toAdd.get(i).setPictureID(rs.getInt(1));
                rs.close();
            }
        } catch (SQLException e) {
            pstmt = null;
            sblob = null;
            rs = null;
            throw e;
        }
    }

    @Override
    public void deletePicture(Picture toDelete)
            throws PictureInPictureQuestionException, SQLException {
        try {

            pstmt = con.prepareStatement(sqlDelete);
            pstmt.setInt(1, toDelete.getPictureID());
            pstmt.execute();

        } catch (SQLException e) {
            pstmt = null;
            if ("23503".equals(e.getSQLState()))
                throw new PictureInPictureQuestionException(e);
            else
                throw e;

        }
    }

    @Override
    public void deletePictures(ArrayList<Picture> toDelete)
            throws PictureInPictureQuestionException, SQLException {
        try {

            pstmt = con.prepareStatement(sqlDeleteAll);

            int len = toDelete.size();
            for (int i = 0; i < len; i++) {
                pstmt.setInt(1, toDelete.get(i).getPictureID());
                pstmt.execute();
            }

        } catch (SQLException e) {
            pstmt = null;

            if ("23503".equals(e.getSQLState()))
                throw new PictureInPictureQuestionException(e);
            else
                throw e;

        }

    }

    @Override
    public Picture getPictureByID(Integer pictureID) throws SQLException {
        try {
            pic = new Picture();

            pstmt = con.prepareStatement(sqlGet);
            pstmt.setInt(1, pictureID);
            rs = pstmt.executeQuery();

            rs.next();
            pic.setName(rs.getString("Name"));
            pic.setDescription(rs.getString("Description"));
            pic.setSettings(rs.getString("Settings"));
            pic.setMimeType(rs.getString("MimeType"));
            Blob a = rs.getBlob("Content");
            pic.setContent(a.getBytes(1, (int) a.length()));
            pic.setPictureID(rs.getInt("pictureID"));
            rs.close();

        } catch (SQLException e) {
            pic = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return pic;
    }

    @Override
    public boolean existsPictureByName(String name) throws SQLException {
        boolean flag = true;

        try {
            pstmt = con.prepareStatement(sqlExistsName);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            flag = rs.next();

        } catch (SQLException e) {
            pstmt = null;
            rs = null;
            flag = true;
            throw e;
        }
        return flag;
    }

    @Override
    public ArrayList<Picture> getPicturesByTestID(Integer testID)
            throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlGetByTest,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pstmt.setInt(1, testID);
            ResultSet rsLocal = pstmt.executeQuery();

            rsLocal.last();
            p_list = new ArrayList<Picture>(rsLocal.getRow());
            rsLocal.beforeFirst();
            while (rsLocal.next()) {
                pic = getPictureByID(rsLocal.getInt("pictureID"));
                p_list.add(pic);
            }
            rsLocal.close();

        } catch (SQLException e) {
            pic = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return p_list;
    }

    public ArrayList<Picture> getAllPictures() throws SQLException {
        try {
            ;
            pstmt = con.prepareStatement(sqlGetAll,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            ResultSet rsLocal = pstmt.executeQuery();

            rsLocal.last();
            p_list = new ArrayList<Picture>(rsLocal.getRow());
            rsLocal.beforeFirst();
            while (rsLocal.next()) {
                pic = getPictureByID(rsLocal.getInt("pictureID"));
                p_list.add(pic);
            }
            rsLocal.close();

        } catch (SQLException e) {

            pic = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return p_list;
    }

    @Override
    public ArrayList<Picture> getPicturesByQuestionID(Integer QuestionID)
            throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlGetByQuestion,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pstmt.setInt(1, QuestionID);
            ResultSet rsLocal = pstmt.executeQuery();
            rsLocal.last();
            p_list = new ArrayList<Picture>(rsLocal.getRow());
            rsLocal.beforeFirst();
            while (rsLocal.next()) {
                pic = getPictureByID(rsLocal.getInt("pictureID"));
                p_list.add(pic);
            }
            rsLocal.close();

        } catch (SQLException e) {
            pic = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return p_list;
    }

    public void addPictureToQuestion(Integer pictureId, Integer questionId)
            throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlAddPicToQuestion);

            pstmt.setInt(1, pictureId);
            pstmt.setInt(2, questionId);
            pstmt.execute();

        } catch (SQLException e) {
            pstmt = null;

            throw e;
        }

    }

    public void deletePictureFromQuestion(Integer pictureId, Integer questionId)
            throws SQLException {
        try {
            pstmt = con.prepareStatement(sqlDelPicFromQuestion);

            pstmt.setInt(1, pictureId);
            pstmt.setInt(2, questionId);
            pstmt.execute();

        } catch (SQLException e) {
            pstmt = null;

            throw e;
        }
    }

    @Override
    public void updatePicture(Picture toUpdate) throws SQLException {
        try {

            sblob = new SerialBlob(toUpdate.getContent());
            pstmt = con.prepareStatement(sqlUpdate);
            pstmt.setString(1, toUpdate.getName());
            pstmt.setString(2, toUpdate.getDescription());
            pstmt.setString(3, toUpdate.getSettings());
            pstmt.setString(4, toUpdate.getMimeType());
            pstmt.setBlob(5, sblob.getBinaryStream());
            pstmt.setInt(6, toUpdate.getPictureID());
            pstmt.execute();

        } catch (SQLException e) {
            pstmt = null;
            sblob = null;
            rs = null;
            throw e;
        }
    }

}
